﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace iHub
{
    class AccountManager
    {
        private dbConnector db = new dbConnector();
        private SqlDataReader read;
        private SqlCommand com;
        private String username = "";
        private String password = "";
        private Boolean usertype = false;
        private Boolean status = true;
        private String question = "";
        private String answer = "";

        public Boolean verifyLogin(Account a)
        {
            String username = a.getUsername();
            String password = a.getPass();
            Boolean allow = false;

            db.connect();

            com = new SqlCommand("select * from Accounts where username = '" + username + "' and password = '" + password + "'", db.getCon());
            read = com.ExecuteReader();

            if (read.HasRows == true)
            {
                allow = true;
            }

            read.Close();
            db.disconnect();

            return allow;
        }

        public void setAccount(Account a)
        {
            String user = a.getUsername();
            String pass = a.getPass();

            db.connect();

            com = new SqlCommand("select * from Accounts where username = '" + user + "' and password = '" + pass + "'", db.getCon());
            read = com.ExecuteReader();

            while (read.Read())
            {
                a.setUsertype(Convert.ToBoolean(read["usertype"]));
            }

            read.Close();
            db.disconnect();
        }

        public Boolean isTerminated(String username)
        {
            String user = username;
            Boolean allowed = false;

            db.connect();

            com = new SqlCommand("select status from Accounts where username = '" + user + "'", db.getCon());
            read = com.ExecuteReader();

            while (read.Read())
            {
                allowed = Convert.ToBoolean(read["status"]);
            }

            read.Close();
            db.disconnect();

            return allowed;
        }

        public String getQuestion(String username)
        {
            String question = "";

            db.connect();

            com = new SqlCommand("select securityquestion from Accounts where username = '" + username + "'", db.getCon());
            read = com.ExecuteReader();

            while (read.Read())
            {
                question = Convert.ToString(read["securityquestion"]);
            }

            read.Close();
            db.disconnect();
            return question;
        }

        public Boolean verifyAnswer(String question, String username, String answer)
        {
            Boolean verified = false;

            db.connect();

            com = new SqlCommand("select count(*) as verify from Accounts where username = '" + username + "' and securityquestion = '" + question + "' and securityanswer = '" + answer + "'", db.getCon());
            read = com.ExecuteReader();

            while (read.Read())
            {
                verified = Convert.ToBoolean(read["verify"]);
            }

            read.Close();
            db.disconnect();

            return verified;
        }

        public DataTable viewLoginHistory()
        {
            DataTable history = new DataTable();
            String type = "";
            DateTime date;
            String d = "";
            String t = "";

            db.connect();

            com = new SqlCommand("select * from Login_History order by date desc, time desc", db.getCon());
            read = com.ExecuteReader();

            history.Columns.Add(new DataColumn("Username", typeof(string)));
            history.Columns.Add(new DataColumn("User Type", typeof(string)));
            history.Columns.Add(new DataColumn("Time of Access", typeof(string)));
            history.Columns.Add(new DataColumn("Date of Access", typeof(string)));

            while (read.Read())
            {
                username = read["username"].ToString();
                usertype = Convert.ToBoolean(read["usertype"]);

                if (usertype)
                {
                    type = "Administrator";
                }
                else
                {
                    type = "Encoder";
                }

                date = Convert.ToDateTime(read["date"]);
                d = date.ToShortDateString();

                t = read["time"].ToString();

                history.Rows.Add(username, type, t, d);
            }

            read.Close();
            db.disconnect();

            return history;
        }

        public void updateLoginHistory(String username, Boolean usertype, DateTime date)
        {
            db.connect();

            com = new SqlCommand("INSERT INTO Login_History (username, date, time, usertype) VALUES (@user, @date, @time, @type)", db.getCon());

            com.Parameters.Add(new SqlParameter("user", username));
            com.Parameters.Add(new SqlParameter("date", Convert.ToDateTime(date.ToLongDateString())));
            com.Parameters.Add(new SqlParameter("time", Convert.ToDateTime(date.ToLongTimeString())));
            com.Parameters.Add(new SqlParameter("type", usertype));

            com.ExecuteNonQuery();

            db.disconnect();
        }

        public DataTable viewUserAccounts()
        {
            DataTable accounts = new DataTable();
            String type = "";
            String stat = "";

            db.connect();

            com = new SqlCommand("select * from Accounts", db.getCon());
            read = com.ExecuteReader();

            accounts.Columns.Add(new DataColumn("Username", typeof(string)));
            accounts.Columns.Add(new DataColumn("Password", typeof(string)));
            accounts.Columns.Add(new DataColumn("User Type", typeof(string)));
            accounts.Columns.Add(new DataColumn("Status", typeof(string)));
            accounts.Columns.Add(new DataColumn("Security Question", typeof(string)));
            accounts.Columns.Add(new DataColumn("Security Answer", typeof(string)));

            while (read.Read())
            {
                username = read["username"].ToString();
                password = read["password"].ToString();
                usertype = Convert.ToBoolean(read["usertype"]);
                status = Convert.ToBoolean(read["status"]);
                question = read["securityquestion"].ToString();
                answer = read["securityanswer"].ToString();

                if (usertype)
                {
                    type = "Administrator";
                }
                else
                {
                    type = "Encoder";
                }

                if (status)
                {
                    stat = "Active";
                }
                else
                {
                    stat = "Inactive";
                }

                accounts.Rows.Add(username, password, type, stat, question, answer);
            }

            read.Close();
            db.disconnect();

            return accounts;
        }

        public void addUserAccount(Account a)
        {
            username = a.getUsername();
            password = a.getPass();
            usertype = a.getUsertype();
            status = true;
            question = a.getQuestion();
            answer = a.getAnswer();

            db.connect();

            com = new SqlCommand("dbo.createAccount", db.getCon());
            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.Add(new SqlParameter("user", username));
            com.Parameters.Add(new SqlParameter("pass", password));
            com.Parameters.Add(new SqlParameter("type", usertype));
            com.Parameters.Add(new SqlParameter("status", status));
            com.Parameters.Add(new SqlParameter("question", question));
            com.Parameters.Add(new SqlParameter("answer", answer));

            com.ExecuteNonQuery();

            db.disconnect();
        }

        public bool findAccount()
        {
            int count=0;
            bool check=false;

            db.connect();

            com = new SqlCommand("select count(*) as counttype from Accounts where usertype = 1", db.getCon());
            read = com.ExecuteReader();

            if (read.Read())
            {
                count = Convert.ToInt32(read["counttype"]);

                if (count >= 2)
                {
                    check = true;
                }
            }

            db.disconnect();

            return check;
        }

        public void terminateAccount(String username)
        {
            db.connect();

            com = new SqlCommand("update Accounts set status = 0 where username = '" + username + "'", db.getCon());
            com.ExecuteNonQuery();

            db.disconnect();
        }

        public void editPassword(String username, String password)
        {
            db.connect();

            com = new SqlCommand("update Accounts set password = '" + password + "' where username = '" + username + "'", db.getCon());
            com.ExecuteNonQuery();

            db.disconnect();
        }

        public void editSecurity(String username, String question, String answer)
        {
            db.connect();

            com = new SqlCommand("update Accounts set securityquestion = '" + question + "', securityanswer = '" + answer + "' where username = '" + username + "'", db.getCon());
            com.ExecuteNonQuery();

            db.disconnect();
        }

        public void editUserAccount(Account a)
        {
            username = a.getUsername();
            password = a.getPass();
            usertype = a.getUsertype();
            status = a.getStatus();
            question = a.getQuestion();
            answer = a.getAnswer();

            db.connect();

            com = new SqlCommand("dbo.editAccount", db.getCon());
            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.Add(new SqlParameter("pass", password));
            com.Parameters.Add(new SqlParameter("type", usertype));
            com.Parameters.Add(new SqlParameter("status", status));
            com.Parameters.Add(new SqlParameter("question", question));
            com.Parameters.Add(new SqlParameter("answer", answer));
            com.Parameters.Add(new SqlParameter("user", username));

            com.ExecuteNonQuery();

            db.disconnect();
        }

        public void incLockdownCount()
        {
            db.connect();

            com = new SqlCommand("update Lockdown_Dump set counter = counter + 1", db.getCon());
            com.ExecuteNonQuery();

            db.disconnect();
        }

        public void resetCounter()
        {
            db.connect();

            com = new SqlCommand("update Lockdown_Dump set counter = 0", db.getCon());
            com.ExecuteNonQuery();

            db.disconnect();
        }

        public int getLockdownCount()
        {
            int i = 0;
            db.connect();

            com = new SqlCommand("select counter from Lockdown_Dump", db.getCon());
            read = com.ExecuteReader();

            while (read.Read())
            {
                i = int.Parse(read["counter"].ToString());
            }

            read.Close();
            db.disconnect();

            return i;
        }

        public ArrayList getUserList(Boolean type, String username)
        {
            Boolean usertype = type;
            String u = username;
            ArrayList list = new ArrayList();

            db.connect();

            com = new SqlCommand("select username from Accounts where usertype = '" + usertype + "' and username != '" + u + "'", db.getCon());
            read = com.ExecuteReader();

            while (read.Read())
            {
                list.Add(Convert.ToString(read["username"]));
            }

            read.Close();
            db.disconnect();

            return list;
        }

        public Account getUser(String user)
        {
            Account a = new Account();
            a.setUsername(user);

            db.connect();

            com = new SqlCommand("select * from Accounts where username = '" + a.getUsername() + "'", db.getCon());
            read = com.ExecuteReader();

            while (read.Read())
            {
                a.setPass(Convert.ToString(read["password"]));
                a.setUsertype(Convert.ToBoolean(read["usertype"]));
                a.setStatus(Convert.ToBoolean(read["status"]));
                a.setQuestion(Convert.ToString(read["securityquestion"]));
                a.setAnswer(Convert.ToString(read["securityanswer"]));
            }

            read.Close();
            db.disconnect();

            return a;
        }

        public Boolean userExists(String user)
        {
            Boolean exist = false;
            String username = user;

            db.connect();

            com = new SqlCommand("select count(*) as verify from Accounts where username = '" + username + "'", db.getCon());
            read = com.ExecuteReader();

            while (read.Read())
            {
                exist = Convert.ToBoolean(read["verify"]);
            }

            read.Close();
            db.disconnect();

            return exist;
        }
    }
}